﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MaxLeaf.Common;
using MaxLeaf.DB;
using System.Data;
using MySql.Data.MySqlClient;
using System.Transactions;

namespace MaxLeaf.DAL.W2K
{
    public class arCustomerDAL : BaseDAL
    {

        private struct Detail
        {
            public string Name;
            public string FItemID;
            public string FullName;
            public string FNumber;
            public string Level;
        }

        private struct CustomerInfo
        {
            public string FtypeID;
            public Detail Provice;
            public Detail City;
            public Detail MDC;
            public Detail Region;
            public Detail Customer;
        }

        private string GetCustFinterID(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, string strK3MasterFinterID)
        {
            //说明： 由于在网站中的t_submessage是全局分发的，所以在网站的FinterID是K3主帐套的
            //       的t_submessage的
            string strServerName = "K3Master";
            ServerInfo serverK3 = ServerInfo.GetServer(strServerName);
            using (DBAccess dbK3Master = new DBAccess(serverK3))
            {
                return new K2K.t_SubMessageDAL().GetFInterID(dbK3Master, dbDestK3Con, strK3MasterFinterID);
            }
        }

        public void Delete(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, string strKeyValue)
        {
            //删除k3数据
            string strUser_id = strKeyValue;
            string strSQL = @"select USERT.User_ID,USERT.User_Name,MDC.ID as MDC_ID,MDC_Name,MDC.region_id,MDC.region_Name,region_dbname
            from mary_users USERT
            inner join mary_mdc MDC on MDC.ID= USERT.MDC_ID
            where USERT.User_ID ='{0}'";
            strSQL = string.Format(strSQL, strUser_id);
            DataTable dtTable = dbOrgWebReadCon.GetDataTable(strSQL);
            foreach (DataRow drRow in dtTable.Rows)
            {
                string strDB = CvtFunc.ToString(drRow["region_dbname"]);
                if (dbDestK3Con.Connection.Database != strDB)
                {
                    LogManager.Debug(string.Format("不在同一数据库！"));
                    return;
                }
            }
            strSQL = @"select FItemID from mary_users where User_ID ='{0}'";
            strSQL = string.Format(strSQL, strUser_id);
            DataRow drDel = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drDel != null)
            {
                if (CvtFunc.ToString(drDel["FItemID"]) != "")
                {
                    strSQL = "Delete from t_Item where FItemID='" + CvtFunc.ToString(drDel["FItemID"]) + "'";
                    dbDestK3Con.ExecuteSql(strSQL);
                    strSQL = "Delete from t_organization where FItemID='" + CvtFunc.ToString(drDel["FItemID"]) + "'";
                    dbDestK3Con.ExecuteSql(strSQL);
                }
            }
        }

        //读网站的内容，向K3中添加, 
        //此处的server是网站的联接
        //此处的drvLog是从网站的log表中读取的行内容
        //客户编码最后是：   01(省份).01（市）.01分区.01（MDC）.顺序号(6位)
        public override int Add(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, string strKeyValue)
        {
            string strUser_id = strKeyValue;
            string strSQL = @"select USERT.User_ID,USERT.User_Name,MDC.ID as MDC_ID,MDC_Name,MDC.region_id,MDC.region_Name,region_dbname,USERT.FItemID,USERT.FTypeID,
                              company_name,shouji,dizhi,youbian,chuanzhen,card_sn,email,office_phone,zhuangtai,content_user,is_use,phone_one
                              from mary_users USERT
                              inner join mary_mdc MDC on MDC.ID= USERT.MDC_ID
                              where USERT.User_ID ='{0}' and ( USERT.FItemID is null or  USERT.FItemID =0 )";

            strSQL = @"SELECT user_main.user_id,user_main.company_name,user_main.mobile,user_main.email,
                        user_info.tax_company_address,user_info.company_zip,user_info.fax,user_info.user_sn,user_info.tax_company_tel,
                        user_info.contact,user_info.tax_bank_name,user_info.tax_bank_accounts,user_info.tax_identifier,user_info.tax_type,user_info.is_month,
                        region_mdc.mdc_name,region_main.region_name,k3_db_main.dbName,region_main_D.region_name as provicename,region_mdc.mdc_id,k3_region_mdc.FitemID
                        FROM user_main
                        inner join user_info on  user_main.user_id=user_info.user_id
                        inner join region_mdc on user_info.mdc_id=region_mdc.mdc_id
                        inner join region_mdc_db on region_mdc_db.province_id = region_mdc.province_id
                        inner join region_main on region_main.region_id= region_mdc.city_mdc_id
                        inner join k3_db_main on region_mdc_db.db_id=k3_db_main.db_id
                        inner join region_main as region_main_D  on region_mdc_db.province_id =region_main_D.region_id
                        inner join k3_region_mdc on k3_region_mdc.region_mdc_id =region_mdc.province_id and k3_region_mdc.region_type='1'
                        where user_main.User_ID ='{0}'";
            strSQL = string.Format(strSQL, strUser_id);
            DataTable dtTable = dbOrgWebReadCon.GetDataTable(strSQL);
            foreach (DataRow drRow in dtTable.Rows)
            {
                string strDB = CvtFunc.ToString(drRow["dbName"]);
                if (dbDestK3Con.Connection.Database != strDB)
                {
                    LogManager.Debug(string.Format("不在同一数据库！"));
                    return 1;
                }

                if (AddCustomer(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drRow))
                {
                    //strSQL = "Delete from mLog where RecID='{0}'";
                    //strSQL = string.Format(strSQL, CvtFunc.ToString(strKeyValue));
                    //dbOrgCon.ExecuteSql(strSQL);
                }
                else
                {
                    throw new MaxLeafException(string.Format("添加客户不成功！"));
                }
            }
            return 1;
        }

        public override int Update(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, string strKeyValue)
        {
            string strUser_id = strKeyValue;
            string strSQL = @"select USERT.User_ID,USERT.User_Name,MDC.ID as MDC_ID,MDC_Name,MDC.region_id,MDC.region_Name,region_dbname,USERT.FItemID,USERT.FTypeID,
                              company_name,shouji,dizhi,youbian,chuanzhen,card_sn,email,office_phone,zhuangtai,content_user ,is_use,phone_one
            from mary_users USERT
            inner join mary_mdc MDC on MDC.ID= USERT.MDC_ID
            where USERT.User_ID ='{0}' ";
           

            strSQL = @"SELECT user_main.user_id,user_main.company_name,user_main.mobile,user_main.email,
                        user_info.tax_company_address,user_info.company_zip,user_info.fax,user_info.user_sn,user_info.tax_company_tel,
                        user_info.contact,user_info.tax_bank_name,user_info.tax_bank_accounts,user_info.tax_identifier,user_info.tax_type,user_info.is_month,
                        region_mdc.mdc_name,region_main.region_name,region_main_D.region_name as provicename,region_mdc.mdc_id,k3_db_main.dbName,user_main.is_delete,k3_region_mdc.FitemID
                        FROM user_main
                        inner join user_info on  user_main.user_id=user_info.user_id
                        inner join region_mdc on user_info.mdc_id=region_mdc.mdc_id
                        inner join region_mdc_db on region_mdc_db.province_id = region_mdc.province_id
                        inner join region_main on region_main.region_id= region_mdc.city_mdc_id
                        inner join k3_db_main on region_mdc_db.db_id=k3_db_main.db_id
                        inner join region_main as region_main_D  on region_mdc_db.province_id =region_main_D.region_id
                        inner join k3_region_mdc on k3_region_mdc.region_mdc_id =region_mdc.province_id and k3_region_mdc.region_type='1'
                        where user_main.User_ID ='{0}'";

            strSQL = string.Format(strSQL, strUser_id);
            DataTable dtTable = dbOrgWebReadCon.GetDataTable(strSQL);
            foreach (DataRow drRow in dtTable.Rows)
            {
                string strDB = CvtFunc.ToString(drRow["dbName"]);
                string strServerName = "K3Master";

                if (dbDestK3Con.Connection.Database != strDB)
                {
                    LogManager.Debug(string.Format("不在同一数据库！"));
                    return 1;
                }
                //ServerInfo serverK3 = ServerInfo.GetServer(strServerName);
                //serverK3.Database = strDB;
                //using (DBAccess mycon = new DBAccess(serverK3))
                //{
                if (UpateCustomer(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drRow))
                {
                    //strSQL = "Delete from mLog where ID='{0}'";
                    //strSQL = string.Format(strSQL, CvtFunc.ToString(drRow["RecID"]));
                    //dbOrgCon.ExecuteSql(strSQL);
                    return 1;
                }
                else
                {
                    throw new MaxLeafException(string.Format("更新客户不成功！"));
                }
                //}
            }
            return 1;
        }
        //INSERT 到 Fitem表，获得新的Fitem，并要回传网站
        private bool AddCity(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            //City的Flevel=2
            string strCity = curCust.City.Name;
            string strFullName = curCust.City.FullName;
            {
                curCust.City.FNumber = curCust.Region.FNumber + "." + GetMaxFnumber(dbDestK3Con, curCust.City.Level, ref curCust);
                string strSQL = @"insert into t_Item (FitemClassID,FNumber,FparentID,Flevel,Fdetail,Fname,FUnUsed,FBrNo,FFullNumber,
                                  FDiff,FDeleted,FshortNumber,FFullName) 
                                  VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
                strSQL = string.Format(strSQL, '1'
                                                , curCust.City.FNumber
                                                , curCust.Region.FItemID
                                                , curCust.City.Level
                                                , 0
                                                , curCust.City.Name
                                                , 0
                                                , 0
                                                , curCust.City.FNumber
                                                , 0
                                                , 0
                                                , curCust.City.FNumber
                                                , curCust.City.FullName);
                dbDestK3Con.ExecuteSql(strSQL);
                strSQL = "select FItemID from t_Item where FitemClassID='1' and FFullNumber='" + curCust.City.FNumber + "' and FFullName='" + curCust.City.FullName + "'";
                curCust.City.FItemID = dbDestK3Con.GetValue(strSQL);

                //更新WEB，回写FItemID到网站的marry_region表
                //strSQL ="update marry_region set FItemID='"+  +"' where region_id='"+ CvtFunc.ToString(drWebRow["region_id"]) +"'";
            }
            return true;
        }

        private bool AddMDC(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            curCust.MDC.FNumber = curCust.City.FNumber + "." + GetMaxFnumber(dbDestK3Con, curCust.MDC.Level, ref curCust);
            string strSQL = @"insert into t_Item (FitemClassID,FNumber,FparentID,Flevel,Fdetail,Fname,FUnUsed,FBrNo,FFullNumber,
                                  FDiff,FDeleted,FshortNumber,FFullName) 
                                  VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
            strSQL = string.Format(strSQL, '1'
                                            , curCust.MDC.FNumber
                                            , curCust.City.FItemID
                                            , curCust.MDC.Level
                                            , 0
                                            , curCust.MDC.Name
                                            , 0
                                            , 0
                                            , curCust.MDC.FNumber
                                            , 0
                                            , 0
                                            , curCust.MDC.FNumber
                                            , curCust.MDC.FullName);
            strSQL += ";select @@identity";
            dbDestK3Con.ExecuteSql(strSQL);
            strSQL = "select FItemID from t_Item where FitemClassID='1' and FFullNumber='" + curCust.MDC.FNumber + "' and FFullName='" + curCust.MDC.FullName + "'";
            string strFnumber = dbDestK3Con.GetValue(strSQL);
            curCust.MDC.FItemID = strFnumber;

            //更新WEB，回写FItemID到网站的mary_mdc表
            strSQL = "update mary_mdc set FItemID='" + curCust.MDC.FItemID + "' where id='" + CvtFunc.ToString(drWebRow["MDC_ID"]) + "'";
            dbOrgWebWriteCon.ExecuteSql(strSQL);
            return true;
        }

        private bool AddCustomer(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow)
        {
            CustomerInfo curCust = new CustomerInfo();
            //获得(省)        
            string strSQL = "select * from t_Item where FitemClassID='1' and Flevel=1 and FDetail=0 and FItemID='" + drWebRow["FitemID"] + "'";
            DataRow drProvince = dbDestK3Con.GetDataFirstRow(strSQL);
            if (drProvince == null) return false;//网站数据有错，返回；错误原因：大区 or 分区 在网站中没有对应关系。

            curCust.Provice.Name = CvtFunc.ToString(drProvince["FName"]);
            curCust.Provice.FullName = CvtFunc.ToString(drProvince["FFullName"]);
            curCust.Provice.FNumber = CvtFunc.ToString(drProvince["FNumber"]);
            curCust.Provice.Level = "1";
            curCust.Provice.FItemID = CvtFunc.ToString(drProvince["FItemID"]);

            strSQL = "select * from region_mdc" ;
            strSQL += " inner join k3_region_mdc on region_mdc.city_mdc_id = k3_region_mdc.region_mdc_id and k3_region_mdc.region_type='2' ";
            strSQL +="  inner join region_main on region_main.region_id=region_mdc.city_mdc_id where region_mdc.mdc_id='" + drWebRow["mdc_id"] + "'";
            DataRow drCity = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drCity != null)
            {
                curCust.City.Name = CvtFunc.ToString(drWebRow["Region_name"]);
                curCust.City.FullName = curCust.Provice.FullName + "_" + curCust.City.Name;
                curCust.City.FItemID = CvtFunc.ToString(drCity["FItemID"]);
                curCust.City.Level = "2";
            }
            
            strSQL = "select * from region_mdc";
            strSQL += " inner join k3_region_mdc on region_mdc.region_mdc_id = k3_region_mdc.region_mdc_id and k3_region_mdc.region_type='3' ";
            strSQL += " inner join region_main on region_main.region_id=region_mdc.region_mdc_id where region_mdc.mdc_id='" + drWebRow["mdc_id"] + "'";
            DataRow drRegion = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drRegion != null)
            {
                curCust.Region.Name = CvtFunc.ToString(drRegion["Region_name"]);
                curCust.Region.FullName = curCust.City.FullName + "_" + CvtFunc.ToString(drRegion["Region_name"]);
                curCust.Region.FNumber = dbDestK3Con.GetValue("select FNumber from t_item where FItemID='" + drRegion["FItemID"] + "'");
                curCust.Region.Level = "3";
                curCust.Region.FItemID = CvtFunc.ToString(drRegion["FItemID"]);
            }

            strSQL = "select * from region_mdc inner join k3_region_mdc on region_mdc.mdc_id=k3_region_mdc.region_mdc_id and region_type=4 where region_mdc.mdc_id='" + drWebRow["mdc_id"] + "'";
            DataRow drMDC = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drMDC != null)
            {
                curCust.MDC.Name = CvtFunc.ToString(drWebRow["MDC_Name"]);
                curCust.MDC.FullName = curCust.Region.FullName + "_" + curCust.MDC.Name;
                curCust.MDC.Level = "4";
                curCust.MDC.FNumber = dbDestK3Con.GetValue ("select FNumber from t_item where FItemID='"+ drMDC["FItemID"] +"'")  ;
                curCust.MDC.FItemID = CvtFunc.ToString(drMDC["FItemID"]);
            }

            curCust.Customer.Name = CvtFunc.ToString(drWebRow["company_name"]);
            curCust.Customer.FullName = curCust.MDC.FullName + "_" + curCust.Customer.Name;
            curCust.Customer.Level = "5";
            if (!checkProvince(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                LogManager.Debug(string.Format("添加省不成功！"));
                return false;
            }

            if (!checkCity(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                LogManager.Debug(string.Format("添加市不成功！"));
                return false;
            }



            if (!CheckMDC(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                LogManager.Debug(string.Format("添加MDC不成功！"));
                return false;
            }

            if (!CheckRegion(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                throw new MaxLeafException(string.Format("添加Region不成功！"));
                return false;
            }

            string strMaxFnumber = GetMaxFnumber(dbDestK3Con, "5", ref curCust);
            curCust.Customer.FNumber = curCust.MDC.FNumber + "." + strMaxFnumber;

            string strFInterID = GetCustFinterID(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, CvtFunc.ToString(drWebRow["tax_type"]));
            curCust.FtypeID = strFInterID;

            AddCustomerDetail(dbDestK3Con, drWebRow, ref curCust);

            #region 更新WEB，回写FItemID到网站的mary_mdc表
            strSQL = "update k3_user_main set FItemID='" + curCust.Customer.FItemID + "' where user_id='" + CvtFunc.ToString(drWebRow["User_ID"]) + "'";
            dbOrgWebWriteCon.ExecuteSql(strSQL);
            #endregion
            return true;
        }

        private static void AddCustomerDetail(DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            string strSQL = "";
            #region 添加新的customer到K3的表中
            strSQL = @"insert into t_Item (FitemClassID,FNumber,FparentID,Flevel,Fdetail,Fname,FUnUsed,FBrNo,FFullNumber,
                                  FDiff,FDeleted,FshortNumber,FFullName) 
                                  VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
            strSQL = string.Format(strSQL, '1'
                                            , curCust.Customer.FNumber
                                            , curCust.MDC.FItemID
                                            , curCust.Customer.Level
                                            , 1
                                            , curCust.Customer.Name
                                            , 0
                                            , 0
                                            , curCust.Customer.FNumber
                                            , 0
                                            , 0
                                            , curCust.Customer.FNumber
                                            , curCust.Customer.FullName);
            dbDestK3Con.ExecuteSql(strSQL);

            strSQL = "select FItemID from t_Item where FitemClassID='1' and FFullNumber='" + curCust.Customer.FNumber + "' and FFullName='" + curCust.Customer.FullName + "'";
            curCust.Customer.FItemID = dbDestK3Con.GetValue(strSQL);


            strSQL = @" insert into t_organization ( Fname,fmobilephone,faddress,fpostalcode,ffax,f_102,femail,fphone,fstatus,fcontact,FtypeID,FItemID,
                                                    FBank,FAccount,FTaxNum,Fnumber,FParentID,FshortNumber,FSaleID,fdeleted)
                        values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',
                                                    '{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}'   )";
            strSQL = string.Format(strSQL, curCust.Customer.Name
                                            , ""
                                            , CvtFunc.ToString(drWebRow["tax_company_address"])
                                            , CvtFunc.ToString(drWebRow["company_zip"])
                                            , CvtFunc.ToString(drWebRow["fax"])
                                            , CvtFunc.ToString(drWebRow["user_sn"])
                                            , ""
                                            , CvtFunc.ToString(drWebRow["tax_company_tel"])
                                            , "1072"//CvtFunc.ToString(drWebRow["zhuangtai"])
                                            , CvtFunc.ToString(drWebRow["contact"])
                                            , CvtFunc.ToString(curCust.FtypeID)
                                            , curCust.Customer.FItemID
                                            , CvtFunc.ToString(drWebRow["tax_bank_name"])
                                            , CvtFunc.ToString(drWebRow["tax_bank_accounts"])
                                            , CvtFunc.ToString(drWebRow["tax_identifier"])
                                            , curCust.Customer.FNumber
                                            , curCust.MDC.FItemID
                                            , CvtFunc.ToRightString(curCust.Customer.FNumber, 6)                                            
                                            , CvtFunc.ToBoolean(drWebRow["is_month"]) ? "101" : "100"
                                            , 0
                                            );
            dbDestK3Con.ExecuteSql(strSQL);
            #endregion
        }

        private string GetMaxFnumber(DBAccess dbDestK3Con, string strLevel, ref CustomerInfo curCust)
        {
            //客户编码最后是：   9.分区(2位).MDC(3位).顺序号(6位)  如: 9.01.001.000001
            string strReturn = "";
            string strSQL = "";
            int intPoint = 1;
            if (strLevel == "5")  // 客户顺序号
            {

                strSQL = "select substring(cast(max(cast( replace(fnumber,'.','') as decimal(18,0))) as varchar(50)),9,6) from t_Item where FitemClassID='1' and Flevel='" + strLevel + "' and fnumber like '" + curCust.MDC.FNumber + "%'";
                intPoint = 6;
            }            
            try
            {
                DataRow drRow = dbDestK3Con.GetDataFirstRow(strSQL);
                if (drRow != null)
                {
                    strReturn = BaseFunctions.FormatWithZero(CvtFunc.ToString(CvtFunc.ToDouble(CvtFunc.ToString(drRow[0])) + 1), intPoint);
                }
            }
            catch (Exception ex)
            {
                strReturn = BaseFunctions.FormatWithZero("1", intPoint);
            }
            return strReturn;
        }

        private bool AddRegion(DBAccess dbDestK3Con, string strRegion)
        {
            //大区不能自动添加。
            return true;
        }

        private bool UpateCustomer(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow)
        {
            CustomerInfo curCust = new CustomerInfo();
            string strSQL = "select * from t_Item where FitemClassID='1' and Flevel=1 and FDetail=0 and FItemID='"+ drWebRow["FItemID"]  +"'";
            DataRow drProvince = dbDestK3Con.GetDataFirstRow(strSQL);
            if (drProvince == null) return false;//网站数据有错，返回；错误原因：大区 or 分区 在网站中没有对应关系。
            curCust.Provice.Name = CvtFunc.ToString(drProvince["FName"]);
            curCust.Provice.FullName = CvtFunc.ToString(drProvince["FFullName"]);
            curCust.Provice.FNumber = CvtFunc.ToString(drProvince["FNumber"]);
            curCust.Provice.Level = "1";
            curCust.Provice.FItemID = CvtFunc.ToString(drProvince["FItemID"]);


            strSQL = "select * from region_mdc";
            strSQL += " inner join k3_region_mdc on region_mdc.city_mdc_id = k3_region_mdc.region_mdc_id and k3_region_mdc.region_type='2'";
            strSQL += " inner join region_main on region_main.region_id=region_mdc.city_mdc_id where region_mdc.mdc_id='" + drWebRow["mdc_id"] + "'";
            DataRow drCity = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drCity != null)
            {
                curCust.City.Name = CvtFunc.ToString(drWebRow["Region_name"]);
                curCust.City.FullName = curCust.Provice.FullName + "_" + curCust.City.Name;
                curCust.City.FItemID = CvtFunc.ToString(drCity["FItemID"]);
                curCust.City.Level = "2";
            }

            strSQL = "select * from region_mdc";
            strSQL += " inner join k3_region_mdc on region_mdc.region_mdc_id = k3_region_mdc.region_mdc_id and k3_region_mdc.region_type='3'";
            strSQL += " inner join region_main on region_main.region_id=region_mdc.region_mdc_id where region_mdc.mdc_id='" + drWebRow["mdc_id"] + "'";
            DataRow drRegion = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drRegion != null)
            {
                curCust.Region.Name = CvtFunc.ToString(drRegion["Region_name"]);
                curCust.Region.FullName = curCust.City.FullName + "_" + CvtFunc.ToString(drRegion["Region_name"]);
                curCust.Region.FNumber = dbDestK3Con.GetValue("select FNumber from t_item where FItemID='" + drRegion["FItemID"] + "'");
                curCust.Region.Level = "3";
                curCust.Region.FItemID = CvtFunc.ToString(drRegion["FItemID"]);
            }

            strSQL = "select * from region_mdc inner join k3_region_mdc on region_mdc.mdc_id=k3_region_mdc.region_mdc_id and region_type=4 where region_mdc.mdc_id='" + drWebRow["mdc_id"] + "'";
            DataRow drMDC = dbOrgWebReadCon.GetDataFirstRow(strSQL);
            if (drMDC != null)
            {
                curCust.MDC.Name = CvtFunc.ToString(drWebRow["MDC_Name"]);
                curCust.MDC.FullName = curCust.Region.FullName + "_" + curCust.MDC.Name;
                curCust.MDC.FItemID = CvtFunc.ToString(drMDC["FItemID"]);
                curCust.MDC.Level = "4";
                curCust.MDC.FNumber = dbDestK3Con.GetValue("select FNumber from t_item where FItemID='" + drMDC["FItemID"] + "'");
            }

            strSQL ="select * from k3_user_main where user_id='"+ drWebRow["user_id"] +"'";
            DataRow drCust = dbOrgWebReadCon.GetDataFirstRow(strSQL);
             
            
            if (drCust != null)
            {
                strSQL = "select fitemid from t_organization where fitemid='" + CvtFunc.ToString(drCust["FItemID"]) + "'";
                DataRow drK3Cust = dbDestK3Con.GetDataFirstRow(strSQL);
                if (drK3Cust == null)
                {
                    if (Add(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, CvtFunc.ToString(drWebRow["user_id"])) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }

                curCust.Customer.Name = CvtFunc.ToString(drWebRow["company_name"]);
                curCust.Customer.FullName = curCust.MDC.FullName + "_" + curCust.Customer.Name;
                curCust.Customer.FItemID = CvtFunc.ToString(drCust["FItemID"]);

                //如果这个customer的MDC发生改变，FullNumber则要跟着发生改变。

                curCust.MDC.FNumber = dbDestK3Con.GetValue("select FNumber from t_item where FItemID='" + curCust.MDC.FItemID + "'")  ;
                curCust.Customer.FNumber = dbDestK3Con.GetValue("select FNumber from t_item where FItemID='" + curCust.Customer.FItemID + "'") ;

                //如果发生改变, 则要重新读取Fnumber
                if ( curCust.Customer.FNumber.IndexOf ( curCust.MDC.FNumber ,  StringComparison.CurrentCultureIgnoreCase )<0)
                {
                    string strMaxFnumber = GetMaxFnumber(dbDestK3Con, "5", ref curCust);               
                    curCust.Customer.FNumber = curCust.MDC.FNumber + "." + strMaxFnumber;                
                }
                curCust.Customer.Level = "5";
            }
            ////不存在就添加
            //if (dbDestK3Con.GetTableDataFirstRow("t_Organization", new Dictionary<string, object>() { { "FitemId", curCust.Customer.FItemID } }) == null)
            //{
            //    strSQL = "select * from t_item where FName='" + curCust.Customer.Name + "' and FitemClassID='1' and FNumber like '9.%' ";
            //    DataRow drCheckCust = dbDestK3Con.GetDataFirstRow(strSQL);
            //    if (drCheckCust == null) //检查有没有以前加过的，如果以前这个客户存在，只是MDC换了，则不能用添加客户，应该只是更新原来的客户。
            //    {
            //        return AddCustomer(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow);
            //    }
            //    else
            //    {
            //        curCust.Customer.FItemID = CvtFunc.ToString(drCheckCust["FItemID"]);
            //    }
            //}
            if (!checkProvince(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                throw new MaxLeafException(string.Format("添加省不成功！"));
                return false;
            }

            if (!checkCity(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                throw new MaxLeafException(string.Format("添加市不成功！"));
                return false;
            }
            if (!CheckMDC(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                throw new MaxLeafException(string.Format("添加MDC不成功！"));
                return false;
            }

            if(!CheckRegion (dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))
            {
                throw new MaxLeafException(string.Format("添加Region不成功！"));
                return false;
            }
            //if (!checkCustomer(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, drWebRow, ref curCust))  //如果这个customer没有改过区域orMDC，则不用重新分配 Fnumber 和 FFullName,否则要重新分配Fnumber和FFullName
            //{
            //    string strMaxFnumber = GetMaxFnumber(dbDestK3Con, "4", ref curCust);
            //    curCust.Customer.FNumber = curCust.MDC.FNumber + "." + strMaxFnumber;
            //}

            string strFInterID = GetCustFinterID(dbOrgWebReadCon, dbOrgWebWriteCon, dbDestK3Con, CvtFunc.ToString(drWebRow["tax_type"]));
            curCust.FtypeID = strFInterID;

            strSQL = @"update t_Item set FitemClassID='{0}'
                                        ,FNumber='{1}'
                                        ,FparentID='{2}'
                                        ,Flevel='{3}'
                                        ,Fdetail='{4}'
                                        ,Fname='{5}'
                                        ,FUnUsed='{6}'
                                        ,FBrNo='{7}'
                                        ,FFullNumber='{8}'
                                        ,FDiff='{9}'
                                        ,FDeleted='{10}'
                                        ,FshortNumber='{11}'
                                        ,FFullName='{12}' 
                                       
                                            where FItemID='{13}'";
            strSQL = string.Format(strSQL, '1'
                                            , curCust.Customer.FNumber
                                            , curCust.MDC.FItemID
                                            , curCust.Customer.Level
                                            , 1
                                            , curCust.Customer.Name
                                            , 0
                                            , 0
                                            , curCust.Customer.FNumber
                                            , 0
                                            , 0
                                            , curCust.Customer.FNumber
                                            , curCust.Customer.FullName
                                            
                                            , curCust.Customer.FItemID);
            dbDestK3Con.ExecuteSql(strSQL);

            //strSQL = "select FItemID from t_Item where FitemClassID='1' and FNumber='" + curCust.Customer.FNumber + "' and FFullNumber='" + curCust.Customer.FullName + "'";
            //DataRow drCheck = k3Con.GetDataFirstRow(strSQL);
            //if (drCheck != null)
            //{
            //    curCust.Customer.FItemID = k3Con.GetValue(strSQL);
            //}
            #region 添加新的customer到K3的表中
            strSQL = @" update t_organization set Fname='{0}'
                                                ,fmobilephone='{1}'
                                                ,faddress='{2}'
                                                ,fpostalcode='{3}'
                                                ,ffax='{4}'
                                                ,f_102='{5}'
                                                ,femail='{6}'
                                                ,fphone='{7}'
                                                ,fstatus='{8}'
                                                ,fcontact='{9}'
                                                ,ftypeID='{10}' 
                                                ,Fnumber='{11}'
                                                ,FParentID='{12}'
                                                ,FshortNumber ='{13}'
                                                ,Fdeleted='{14}'
                                                ,FSaleID='{19}'
                                                ,FBank='{15}'
                                                ,FAccount='{16}'
                                                ,FTaxNum='{17}'
                                                where FItemID='{18}'";
            strSQL = string.Format(strSQL, CvtFunc.ToString(drWebRow["company_name"])
                                            , ""
                                            , CvtFunc.ToString(drWebRow["tax_company_address"])
                                            , CvtFunc.ToString(drWebRow["company_zip"])
                                            , CvtFunc.ToString(drWebRow["fax"])
                                            , CvtFunc.ToString(drWebRow["user_sn"])
                                            , ""
                                            , CvtFunc.ToString(drWebRow["tax_company_tel"])
                                            , "1072"//CvtFunc.ToString(drWebRow["zhuangtai"])
                                            , CvtFunc.ToString(drWebRow["contact"])
                                            , curCust.FtypeID
                                            , curCust.Customer.FNumber
                                            , curCust.MDC.FItemID
                                            , CvtFunc.ToRightString(curCust.Customer.FNumber, 6)   
                                            , 0 //(CvtFunc.ToInt(drWebRow["is_delete"]) == 1 ? 0 : 1)
                                            , CvtFunc.ToString (drWebRow["tax_bank_name"])
                                            , CvtFunc.ToString (drWebRow["tax_bank_accounts"])
                                            , CvtFunc.ToString (drWebRow["tax_identifier"])
                                            , curCust.Customer.FItemID
                                            , CvtFunc.ToBoolean(drWebRow["is_month"]) ? "101" : "100"
                                            );
            dbDestK3Con.ExecuteSql(strSQL);
            #endregion

            #region 更新WEB，回写FItemID到网站的mary_mdc表
            strSQL = "update k3_user_main set FItemID='" + curCust.Customer.FItemID + "' where user_id='" + CvtFunc.ToString(drWebRow["User_ID"]) + "'";
            dbOrgWebWriteCon.ExecuteSql(strSQL);
            #endregion
            return true;
        }


        #region 检查客户
        private bool checkCustomer(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            string strSQL = "select A.* from t_item A inner join t_Organization B on A.FItemID=B.FItemID where A.FitemClassID=1 ";
            strSQL += " and A.FItemID='" + curCust.Customer.FItemID + "' and FFullName like '" + curCust.MDC.FullName + "%'"; // and Fnumber='"+ curCust.Customer.FNumber +"'";
            DataRow drCust = dbDestK3Con.GetDataFirstRow(strSQL);
            if (drCust == null) return false;  // Customer在K3中不存在。
            curCust.Customer.FItemID = CvtFunc.ToString(drCust["FItemID"]);
            curCust.Customer.FNumber = CvtFunc.ToString(drCust["Fnumber"]);
            return true;
        }
        #endregion

        #region 检查大区
        private bool checkProvince(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            string strSQL = "select * from t_item where FitemClassID=1 and FItemId='" + curCust.Provice.FItemID + "'";
            DataRow drRegion = dbDestK3Con.GetDataFirstRow(strSQL);
            if (drRegion == null) return false;  // Region在K3中不存在，不允许自动添加.因为大区是要对应数据库的。
            curCust.Provice.FItemID = CvtFunc.ToString(drRegion["FItemID"]);
            curCust.Provice.FNumber = CvtFunc.ToString(drRegion["Fnumber"]);
            return true;
        }
        #endregion

        #region 检查城区
        private bool checkCity(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            string strSql = "select * from t_Item where FitemclassID=1 and Flevel='2' and FFullName='{0}'";
            strSql = string.Format(strSql, curCust.City.FullName);
            DataRow drCity = dbDestK3Con.GetDataFirstRow(strSql);
            if (drCity == null)  //不存在分区就要添加
            {
                return false;
            }
            else
            {
                return true ;
            }
        }
        #endregion

        #region 检查MDC
        private bool CheckMDC(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            string strSql = "select * from t_Item where FitemclassID=1 and Flevel='4' and FFullName='{0}'";
            strSql = string.Format(strSql, curCust.MDC.FullName);
            DataRow drMDC = dbDestK3Con.GetDataFirstRow(strSql);
            if (drMDC == null)  //不存在分区就要添加
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 检查Region
        private bool CheckRegion(DBAccess dbOrgWebReadCon, DBAccess dbOrgWebWriteCon, DBAccess dbDestK3Con, DataRow drWebRow, ref CustomerInfo curCust)
        {
            string strSql = "select * from t_Item where FitemclassID=1 and Flevel='3' and FFullName='{0}'";
            strSql = string.Format(strSql, curCust.Region.FullName);
            DataRow drMDC = dbDestK3Con.GetDataFirstRow(strSql);
            if (drMDC == null)  //不存在分区就要添加
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion
    }
}
